package org.vehicle.util;

import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import org.springframework.web.multipart.commons.CommonsMultipartFile;

public class ExcelUtil {
	private static Logger logger = LogManager.getLogger(ExcelUtil.class.getName());

	public static Map<String, Object> getExcelData(HttpServletRequest req){
		Map<String, Object> dateMap=new HashMap<String, Object>();
		MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) req; //
		if(multipartRequest.getFile("excelFile") == null){// 文件为空报空指针异常
			dateMap.put("errorMsg", "文件不能为空");
			return dateMap;
		}
		CommonsMultipartFile orginalFile = (CommonsMultipartFile) multipartRequest
				.getFile("excelFile");//
		// 创建相关的文件流对象
		InputStream is = null;
		try{
			 is = orginalFile.getInputStream();
			 Workbook workbook = WorkbookFactory.create(is);  
			 Sheet sheet = workbook.getSheetAt(0);  //示意访问sheet  
			 	int totalRows = sheet.getPhysicalNumberOfRows();
				if(totalRows == 0){
					dateMap.put("errorMsg", "文件不能为空");
					return dateMap;
				}
				if(totalRows == 1 && sheet.getRow(0) != null){
					dateMap.put("errorMsg", "文件不能为空");
					return dateMap;
				}
				if(totalRows > 500){
					dateMap.put("errorMsg", "文件行数不能超出500");
					return dateMap;
				}
			List<Map<String, String>> dataLst = new ArrayList<Map<String, String>>();
			
			/** 得到Excel的行数 */
			int rowCount = sheet.getPhysicalNumberOfRows();
		
			 /** 也可以通过得到最后一行数*/
	        int lastRowNum = sheet.getLastRowNum();
	        lastRowNum=lastRowNum+1;
			
			/** 循环Excel的行 跳过第一行 */
			for (int r = 1; r < lastRowNum+1; r++) {
				Row row = sheet.getRow(r);
				if (row == null) {
					continue;
				}
				Map<String, String> cellMap= new HashMap();
				cellMap.put("rowNo",r+"");/** 放入行号，取值直接通过rowNo */
				/** 循环Excel的列 */
				for (int c = 0; c < row.getLastCellNum(); c++) {
					Cell cell = row.getCell(c);
					String cellValue = "";
					if (null != cell) {
						// 以下是判断数据的类型
						switch (cell.getCellType()) {
						// XSSFCell可以达到相同的效果
						case HSSFCell.CELL_TYPE_NUMERIC: // 数字
//							double d = cell.getNumericCellValue();											
							if (HSSFDateUtil.isCellDateFormatted(cell)) {// 日期类型
								double d = cell.getNumericCellValue();	
								Date date = HSSFDateUtil.getJavaDate(d);
								cellValue = new SimpleDateFormat(
										"yyyy-MM-dd HH:mm:ss").format(date);
							}
							else {// 数值类型					 
								cellValue = String.valueOf(cell.getNumericCellValue());
								DecimalFormat df = new DecimalFormat("#.##########");
				                cellValue=df.format(Double.valueOf(cellValue));
							}
							
							break;
						case HSSFCell.CELL_TYPE_STRING: // 字符串
							cellValue = cell.getStringCellValue();
							break;
						case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
							cellValue = cell.getBooleanCellValue() + "";
							break;
						case HSSFCell.CELL_TYPE_FORMULA: // 公式
							cellValue = cell.getCellFormula() + "";
							break;
						case HSSFCell.CELL_TYPE_BLANK: // 空值
							cellValue = "";
							break;
						case HSSFCell.CELL_TYPE_ERROR: // 故障
							cellValue = "非法字符";
							break;
						default:
							cellValue = "未知类型";
							break;
						}
					}
					cellMap.put("col"+c,cellValue);
				}
				dataLst.add(cellMap);
			}
			dateMap.put("dataList", dataLst);
			return dateMap;
		} catch(Exception e){
			logger.error(e.getMessage());
			dateMap.put("errorMsg", "文件解析失败");
			return dateMap;
		} finally{
			if(is != null){
				try{
					is.close();
				} catch(Exception e){
					is = null;
				}
			}
		}
	}
}
